﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MxWeiXinPF.DBUtility;

namespace MxWeiXinPF.DAL
{
    public partial class wx_dt_user_BaseData
    {
        public int addUserInfo(int wid, string openid, int subscribeStatusDesc, string nickname, int sex, string language, string city, string province, string country, string headimgul, string remark, string unionid, string parentopenid, int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into dt_users(");
            builder.Append("[wid],[wxOpenId],[subscribe],[nick_name],wxName,[sex],[language],[city],[province],[country],[avatar],[subscribe_time],[unsubscribe_time],[remark],[unionid],[isweixin],[group_id],[user_name],[password],[parentopenid],[shopid])");
            builder.Append(" values (");
            builder.Append("@wid,@openid,@subscribeStatusDesc,@nickname,@nickname,@sex,@language,@city,@province,@country,@headimgul,getdate(),null,@remark,@unionid,'1','1',@openid,'123456',@parentopenid,@shopid)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@subscribeStatusDesc", SqlDbType.Int, 4), new SqlParameter("@nickname", SqlDbType.VarChar, 50), new SqlParameter("@sex", SqlDbType.Int, 4), new SqlParameter("@language", SqlDbType.VarChar, 50), new SqlParameter("@city", SqlDbType.NVarChar, 20), new SqlParameter("@province", SqlDbType.NVarChar, 20), new SqlParameter("@country", SqlDbType.NVarChar, 50), new SqlParameter("@headimgul", SqlDbType.VarChar, 500), new SqlParameter("@remark", SqlDbType.VarChar, 500), new SqlParameter("@unionid", SqlDbType.VarChar, 100), new SqlParameter("@parentopenid", SqlDbType.VarChar, 100), new SqlParameter("@shopid", SqlDbType.Int, 4) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = subscribeStatusDesc;
            cmdParms[3].Value = nickname;
            cmdParms[4].Value = sex;
            cmdParms[5].Value = language;
            cmdParms[6].Value = city;
            cmdParms[7].Value = province;
            cmdParms[8].Value = country;
            cmdParms[9].Value = headimgul;
            cmdParms[10].Value = remark;
            cmdParms[11].Value = unionid;
            cmdParms[12].Value = parentopenid;
            cmdParms[13].Value = shopid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int addUserInfoForScan(int wid, string openid, int subscribeStatusDesc, string nickname, int sex, string language, string city, string province, string country, string headimgul, string remark, string unionid, string parentopenid, int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into dt_users(");
            builder.Append("[wid],[wxOpenId],[subscribe],[nick_name],wxName,[sex],[language],[city],[province],[country],[avatar],[subscribe_time],[unsubscribe_time],[remark],[unionid],[isweixin],[group_id],[user_name],[password],[parentopenid],[shopid])");
            builder.Append(" values (");
            builder.Append("@wid,@openid,@subscribeStatusDesc,@nickname,@nickname,@sex,@language,@city,@province,@country,@headimgul,getdate(),null,@remark,@unionid,'1','1',@openid,'123456',@parentopenid,@shopid)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@subscribeStatusDesc", SqlDbType.Int, 4), new SqlParameter("@nickname", SqlDbType.VarChar, 50), new SqlParameter("@sex", SqlDbType.Int, 4), new SqlParameter("@language", SqlDbType.VarChar, 50), new SqlParameter("@city", SqlDbType.NVarChar, 20), new SqlParameter("@province", SqlDbType.NVarChar, 20), new SqlParameter("@country", SqlDbType.NVarChar, 50), new SqlParameter("@headimgul", SqlDbType.VarChar, 500), new SqlParameter("@remark", SqlDbType.VarChar, 500), new SqlParameter("@unionid", SqlDbType.VarChar, 100), new SqlParameter("@parentopenid", SqlDbType.VarChar, 100), new SqlParameter("@shopid", SqlDbType.Int, 4) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = subscribeStatusDesc;
            cmdParms[3].Value = nickname;
            cmdParms[4].Value = sex;
            cmdParms[5].Value = language;
            cmdParms[6].Value = city;
            cmdParms[7].Value = province;
            cmdParms[8].Value = country;
            cmdParms[9].Value = headimgul;
            cmdParms[10].Value = remark;
            cmdParms[11].Value = unionid;
            cmdParms[12].Value = parentopenid;
            cmdParms[13].Value = shopid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int addUserInfoOauth(int wid, string openid, string nickname, int sex, string language, string city, string province, string country, string headimgul, string unionid, int shopid, string parentopenid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into dt_users(");
            builder.Append("[wid],[wxOpenId],[nick_name],[wxName],[sex],[language],[city],[province],[country],[avatar],[unionid],[isweixin],[group_id],[user_name],[password],parentopenid,shopid)");
            builder.Append(" values (");
            builder.Append("@wid,@openid,@nickname,@nickname,@sex,@language,@city,@province,@country,@headimgul,@unionid,'1','1',@openid,'123456',@parentopenid,@shopid)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), 
                new SqlParameter("@nickname", SqlDbType.VarChar, 50), new SqlParameter("@sex", SqlDbType.Int, 4), 
                new SqlParameter("@language", SqlDbType.VarChar, 50), new SqlParameter("@city", SqlDbType.NVarChar, 20),
                new SqlParameter("@province", SqlDbType.NVarChar, 20), new SqlParameter("@country", SqlDbType.NVarChar, 50), 
                new SqlParameter("@headimgul", SqlDbType.VarChar, 500), new SqlParameter("@unionid", SqlDbType.VarChar, 100), 
                new SqlParameter("@parentopenid", SqlDbType.VarChar, 100),new SqlParameter("@shopid",SqlDbType.Int,4) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = nickname;
            cmdParms[3].Value = sex;
            cmdParms[4].Value = language;
            cmdParms[5].Value = city;
            cmdParms[6].Value = province;
            cmdParms[7].Value = country;
            cmdParms[8].Value = headimgul;
            cmdParms[9].Value = unionid;
            cmdParms[10].Value = parentopenid;
            cmdParms[11].Value = shopid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool ExistsWidAndOpenid(int wid, string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from dt_users");
            builder.Append(" where wid=@wid and wxOpenId =@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetDateSetFromDtManager(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select real_name ,UPPER(substring(dbo.[fn_ChineseToSpell](real_name),1,1)) as zjsx,id  from dt_manager ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by zjsx");
            return DbHelperSQL.Query(builder.ToString());
        }

        public MxWeiXinPF.Model.users GetModel(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 *,FLOOR(datediff(DY,birthday,getdate())/365.25) AS Age from dt_users ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            MxWeiXinPF.Model.users users = new MxWeiXinPF.Model.users();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                if (set.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    users.id = int.Parse(set.Tables[0].Rows[0]["id"].ToString());
                }
                if (set.Tables[0].Rows[0]["group_id"].ToString() != "")
                {
                    users.group_id = int.Parse(set.Tables[0].Rows[0]["group_id"].ToString());
                }
                users.user_name = set.Tables[0].Rows[0]["user_name"].ToString();
                users.password = set.Tables[0].Rows[0]["password"].ToString();
                users.salt = set.Tables[0].Rows[0]["salt"].ToString();
                users.email = set.Tables[0].Rows[0]["email"].ToString();
                users.nick_name = set.Tables[0].Rows[0]["nick_name"].ToString();
                users.avatar = set.Tables[0].Rows[0]["avatar"].ToString();
                users.sex = set.Tables[0].Rows[0]["sex"].ToString();
                if (set.Tables[0].Rows[0]["birthday"].ToString() != "")
                {
                    users.birthday = DateTime.Parse(set.Tables[0].Rows[0]["birthday"].ToString());
                }
                users.telphone = set.Tables[0].Rows[0]["telphone"].ToString();
                users.mobile = set.Tables[0].Rows[0]["mobile"].ToString();
                users.qq = set.Tables[0].Rows[0]["qq"].ToString();
                users.address = set.Tables[0].Rows[0]["address"].ToString();
                users.safe_question = set.Tables[0].Rows[0]["safe_question"].ToString();
                users.safe_answer = set.Tables[0].Rows[0]["safe_answer"].ToString();
                if (set.Tables[0].Rows[0]["amount"].ToString() != "")
                {
                    users.amount = decimal.Parse(set.Tables[0].Rows[0]["amount"].ToString());
                }
                if (set.Tables[0].Rows[0]["point"].ToString() != "")
                {
                    users.point = int.Parse(set.Tables[0].Rows[0]["point"].ToString());
                }
                if (set.Tables[0].Rows[0]["exp"].ToString() != "")
                {
                    users.exp = int.Parse(set.Tables[0].Rows[0]["exp"].ToString());
                }
                if (set.Tables[0].Rows[0]["status"].ToString() != "")
                {
                    users.status = int.Parse(set.Tables[0].Rows[0]["status"].ToString());
                }
                if (set.Tables[0].Rows[0]["reg_time"].ToString() != "")
                {
                    users.reg_time = DateTime.Parse(set.Tables[0].Rows[0]["reg_time"].ToString());
                }
                users.reg_ip = set.Tables[0].Rows[0]["reg_ip"].ToString();
                if ((set.Tables[0].Rows[0]["isweixin"] != null) && (set.Tables[0].Rows[0]["isweixin"].ToString() != ""))
                {
                    users.isweixin = new int?(int.Parse(set.Tables[0].Rows[0]["isweixin"].ToString()));
                }
                if ((set.Tables[0].Rows[0]["wid"] != null) && (set.Tables[0].Rows[0]["wid"].ToString() != ""))
                {
                    users.wid = new int?(int.Parse(set.Tables[0].Rows[0]["wid"].ToString()));
                }
                if (set.Tables[0].Rows[0]["wxOpenId"] != null)
                {
                    users.wxOpenId = set.Tables[0].Rows[0]["wxOpenId"].ToString();
                }
                if (set.Tables[0].Rows[0]["wxName"] != null)
                {
                    users.wxName = set.Tables[0].Rows[0]["wxName"].ToString();
                }
                users.subscribe = set.Tables[0].Rows[0]["subscribe"].ToString();
                if (set.Tables[0].Rows[0]["truename"] != null)
                {
                    users.truename = set.Tables[0].Rows[0]["truename"].ToString();
                }
                if (set.Tables[0].Rows[0]["jobtype"] != null)
                {
                    users.jobtype = set.Tables[0].Rows[0]["jobtype"].ToString();
                }
                if (set.Tables[0].Rows[0]["age"] != null)
                {
                    users.age = set.Tables[0].Rows[0]["age"].ToString();
                }
                users.parentopenid = set.Tables[0].Rows[0]["parentopenid"].ToString();
                if ((set.Tables[0].Rows[0]["shopid"] != null) && (set.Tables[0].Rows[0]["shopid"].ToString() != ""))
                {
                    users.shopid = int.Parse(set.Tables[0].Rows[0]["shopid"].ToString());
                }
                return users;
            }
            return null;
        }

        public MxWeiXinPF.Model.users GetModelByOpenID(string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 *,FLOOR(datediff(DY,birthday,getdate())/365.25) AS Age from dt_users ");
            builder.Append(" where wxopenid=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = openid;
            MxWeiXinPF.Model.users users = new MxWeiXinPF.Model.users();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                if (set.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    users.id = int.Parse(set.Tables[0].Rows[0]["id"].ToString());
                }
                if (set.Tables[0].Rows[0]["group_id"].ToString() != "")
                {
                    users.group_id = int.Parse(set.Tables[0].Rows[0]["group_id"].ToString());
                }
                users.user_name = set.Tables[0].Rows[0]["user_name"].ToString();
                users.password = set.Tables[0].Rows[0]["password"].ToString();
                users.salt = set.Tables[0].Rows[0]["salt"].ToString();
                users.email = set.Tables[0].Rows[0]["email"].ToString();
                users.nick_name = set.Tables[0].Rows[0]["nick_name"].ToString();
                users.avatar = set.Tables[0].Rows[0]["avatar"].ToString();
                users.sex = set.Tables[0].Rows[0]["sex"].ToString();
                if (set.Tables[0].Rows[0]["birthday"].ToString() != "")
                {
                    users.birthday = DateTime.Parse(set.Tables[0].Rows[0]["birthday"].ToString());
                }
                users.telphone = set.Tables[0].Rows[0]["telphone"].ToString();
                users.mobile = set.Tables[0].Rows[0]["mobile"].ToString();
                users.qq = set.Tables[0].Rows[0]["qq"].ToString();
                users.address = set.Tables[0].Rows[0]["address"].ToString();
                users.safe_question = set.Tables[0].Rows[0]["safe_question"].ToString();
                users.safe_answer = set.Tables[0].Rows[0]["safe_answer"].ToString();
                if (set.Tables[0].Rows[0]["amount"].ToString() != "")
                {
                    users.amount = decimal.Parse(set.Tables[0].Rows[0]["amount"].ToString());
                }
                if (set.Tables[0].Rows[0]["point"].ToString() != "")
                {
                    users.point = int.Parse(set.Tables[0].Rows[0]["point"].ToString());
                }
                if (set.Tables[0].Rows[0]["exp"].ToString() != "")
                {
                    users.exp = int.Parse(set.Tables[0].Rows[0]["exp"].ToString());
                }
                if (set.Tables[0].Rows[0]["status"].ToString() != "")
                {
                    users.status = int.Parse(set.Tables[0].Rows[0]["status"].ToString());
                }
                if (set.Tables[0].Rows[0]["reg_time"].ToString() != "")
                {
                    users.reg_time = DateTime.Parse(set.Tables[0].Rows[0]["reg_time"].ToString());
                }
                users.reg_ip = set.Tables[0].Rows[0]["reg_ip"].ToString();
                if ((set.Tables[0].Rows[0]["isweixin"] != null) && (set.Tables[0].Rows[0]["isweixin"].ToString() != ""))
                {
                    users.isweixin = new int?(int.Parse(set.Tables[0].Rows[0]["isweixin"].ToString()));
                }
                if ((set.Tables[0].Rows[0]["wid"] != null) && (set.Tables[0].Rows[0]["wid"].ToString() != ""))
                {
                    users.wid = new int?(int.Parse(set.Tables[0].Rows[0]["wid"].ToString()));
                }
                if (set.Tables[0].Rows[0]["wxOpenId"] != null)
                {
                    users.wxOpenId = set.Tables[0].Rows[0]["wxOpenId"].ToString();
                }
                if (set.Tables[0].Rows[0]["wxName"] != null)
                {
                    users.wxName = set.Tables[0].Rows[0]["wxName"].ToString();
                }
                users.subscribe = set.Tables[0].Rows[0]["subscribe"].ToString();
                if (set.Tables[0].Rows[0]["truename"] != null)
                {
                    users.truename = set.Tables[0].Rows[0]["truename"].ToString();
                }
                if (set.Tables[0].Rows[0]["jobtype"] != null)
                {
                    users.jobtype = set.Tables[0].Rows[0]["jobtype"].ToString();
                }
                if (set.Tables[0].Rows[0]["age"] != null)
                {
                    users.age = set.Tables[0].Rows[0]["age"].ToString();
                }
                users.parentopenid = set.Tables[0].Rows[0]["parentopenid"].ToString();
                if ((set.Tables[0].Rows[0]["shopid"] != null) && (set.Tables[0].Rows[0]["shopid"].ToString() != ""))
                {
                    users.shopid = int.Parse(set.Tables[0].Rows[0]["shopid"].ToString());
                }
                return users;
            }
            return null;
        }

        public string GetNickName(string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select top 1 nick_name from dt_users");
            builder.Append(" where wxopenid='" + openid + "'");
            string str = Convert.ToString(DbHelperSQL.GetSingle(builder.ToString()));
            if (string.IsNullOrEmpty(str))
            {
                return "";
            }
            return str;
        }

        public bool isNullNickName(int wid, string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from dt_users");
            builder.Append(" where wid=@wid and wxOpenId =@openid and isnull(nick_name,'')=''");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public bool isNullUserBaseInfo(string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from dt_users");
            builder.Append(" where wxOpenId =@openid and isnull(truename,'')<>'' and isnull(mobile,'')<>'' and isnull(jobtype,'')<>''");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = openid;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public string SelectIDByOpenid(string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select id from dt_users ");
            builder.Append(" where wxopenid=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = openid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return null;
            }
            return single.ToString().Trim();
        }

        public string SelectOpenid(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select wxOpenId from dt_users ");
            builder.Append(" where id=@id ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return null;
            }
            return single.ToString().Trim();
        }

        public string SelectParentOpenIDByID(string id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select parentopenid from dt_users ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = id;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return null;
            }
            return single.ToString().Trim();
        }

        public string SelectParentOpenIDByOpenID(string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select parentopenid from dt_users ");
            builder.Append(" where wxopenid=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = openid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return null;
            }
            return single.ToString().Trim();
        }

        public int SelectShopid(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select shopid from dt_users ");
            builder.Append(" where id=@id ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int SelectShopIDByID(string id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ShopID from dt_users ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = id;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int SelectShopIDByOpenid(string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ShopID from dt_users ");
            builder.Append(" where wxopenid=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = openid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public string SelectShopNameByShopid(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select real_name from dt_manager ");
            builder.Append(" where id=@id ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return null;
            }
            return single.ToString().Trim();
        }

        public int updateNoUserInfo(int wid, string openid, int subscribeStatusDesc, string parentopenid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update dt_users set ");
            builder.Append("[subscribe]=@subscribeStatusDesc,[subscribe_time]=@subscribe_time,[unsubscribe_time]=@unsubscribe_time ");
            builder.Append(" where wid=@wid and wxOpenId=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@subscribeStatusDesc", SqlDbType.Int, 4), new SqlParameter("@subscribe_time", SqlDbType.DateTime), new SqlParameter("@unsubscribe_time", SqlDbType.DateTime), new SqlParameter("@parentopenid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = subscribeStatusDesc;
            if (subscribeStatusDesc == 1)
            {
                cmdParms[3].Value = DateTime.Now;
                cmdParms[4].Value = null;
            }
            else
            {
                cmdParms[3].Value = null;
                cmdParms[4].Value = DateTime.Now;
            }
            cmdParms[5].Value = parentopenid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int updateNoUserInfoForScan(int wid, string openid, string parentopenid, int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update dt_users set ");
            builder.Append(" parentopenid=@parentopenid,shopid=@shopid");
            builder.Append(" where wid=@wid and wxOpenId=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@parentopenid", SqlDbType.VarChar, 100), new SqlParameter("@shopid", SqlDbType.Int, 4) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = parentopenid;
            cmdParms[3].Value = shopid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int updateNoUserInfoOauth(int wid, string openid, string nickname, int sex, string language, string city, string province, string country, string headimgul, string unionid, int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update dt_users set ");
            builder.Append("[nick_name]=@nickname,wxName=@nickname,[sex]=@sex,[language]=@language,[city]=@city,[province]=@province,[country]=@country,[avatar]=@headimgul,[unionid]=@unionid");
            builder.Append(" where wid=@wid and wxOpenId=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@nickname", SqlDbType.VarChar, 50), new SqlParameter("@sex", SqlDbType.Int, 4), new SqlParameter("@language", SqlDbType.VarChar, 50), new SqlParameter("@city", SqlDbType.NVarChar, 20), new SqlParameter("@province", SqlDbType.NVarChar, 20), new SqlParameter("@country", SqlDbType.NVarChar, 50), new SqlParameter("@headimgul", SqlDbType.VarChar, 500), new SqlParameter("@unionid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = nickname;
            cmdParms[3].Value = sex;
            cmdParms[4].Value = language;
            cmdParms[5].Value = city;
            cmdParms[6].Value = province;
            cmdParms[7].Value = country;
            cmdParms[8].Value = headimgul;
            cmdParms[9].Value = unionid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int UpdatePidAndShopid(string id, string parentopenid, int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update dt_users set ");
            builder.Append(" parentopenid=@parentopenid,shopid=@shopid");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@parentopenid", SqlDbType.VarChar, 100), new SqlParameter("@shopid", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            cmdParms[1].Value = parentopenid;
            cmdParms[2].Value = shopid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int UpdateUserInfo(int wid, string openid, int subscribeStatusDesc, string nickname, int sex, string language, string city, string province, string country, string headimgul, string remark, string unionid, string parentopenid, int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update dt_users set ");
            builder.Append("[subscribe]=@subscribeStatusDesc,[subscribe_time]=@subscribe_time,[unsubscribe_time]=@unsubscribe_time ");
            builder.Append(" ,[nick_name]=@nickname,[wxName]=@nickname,[sex]=@sex,language=@language ");
            builder.Append(" ,[city]=@city,[province]=@province,[country]=@country,avatar=@headimgul ");
            builder.Append(" ,[unionid]=@unionid,[remark]=@remark ");
            builder.Append(" where wid=@wid and wxOpenId=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@subscribeStatusDesc", SqlDbType.Int, 4), new SqlParameter("@nickname", SqlDbType.VarChar, 50), new SqlParameter("@sex", SqlDbType.Int, 4), new SqlParameter("@language", SqlDbType.VarChar, 50), new SqlParameter("@city", SqlDbType.NVarChar, 20), new SqlParameter("@province", SqlDbType.NVarChar, 20), new SqlParameter("@country", SqlDbType.NVarChar, 50), new SqlParameter("@headimgul", SqlDbType.VarChar, 500), new SqlParameter("@remark", SqlDbType.VarChar, 500), new SqlParameter("@unionid", SqlDbType.VarChar, 100), new SqlParameter("@parentopenid", SqlDbType.VarChar, 100), new SqlParameter("@subscribe_time", SqlDbType.DateTime), new SqlParameter("@unsubscribe_time", SqlDbType.DateTime) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = subscribeStatusDesc;
            cmdParms[3].Value = nickname;
            cmdParms[4].Value = sex;
            cmdParms[5].Value = language;
            cmdParms[6].Value = city;
            cmdParms[7].Value = province;
            cmdParms[8].Value = country;
            cmdParms[9].Value = headimgul;
            cmdParms[10].Value = remark;
            cmdParms[11].Value = unionid;
            cmdParms[12].Value = parentopenid;
            if (subscribeStatusDesc == 1)
            {
                cmdParms[13].Value = DateTime.Now;
                cmdParms[14].Value = null;
            }
            else
            {
                cmdParms[13].Value = null;
                cmdParms[14].Value = DateTime.Now;
            }
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int UpdateUserInfoUnSubscribe(int wid, string openid, int subscribeStatusDesc)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update dt_users set ");
            builder.Append("[subscribe]=@subscribeStatusDesc,[subscribe_time]=@subscribe_time,[unsubscribe_time]=@unsubscribe_time ");
            builder.Append(" where wid=@wid and wxOpenId=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@subscribeStatusDesc", SqlDbType.Int, 4), new SqlParameter("@subscribe_time", SqlDbType.DateTime), new SqlParameter("@unsubscribe_time", SqlDbType.DateTime) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = subscribeStatusDesc;
            if (subscribeStatusDesc == 1)
            {
                cmdParms[3].Value = DateTime.Now;
                cmdParms[4].Value = null;
            }
            else
            {
                cmdParms[3].Value = null;
                cmdParms[4].Value = DateTime.Now;
            }
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

    }
}
